# -*- coding: utf-8 -*-
import csv
import pymysql
import re
# from twisted.enterprise import adbapi
# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html
re_com = re.compile("\((\d*)\)")


class StockPipeline(object):

    # def __init__(self):
    #     self.files = {}
    #
    # @classmethod
    # def from_crawler(cls, crawler):
    #     pipeline = cls()
    #     crawler.signals.connect(pipeline.spider_opened, signals.spider_opened)
    #     crawler.signals.connect(pipeline.spider_closed, signals.spider_closed)
    #     return pipeline
    #
    # def spider_opened(self, spider):
    #     file = open('%s_items.csv' % spider.name, 'w+b')
    #     self.files[spider] = file
    #     self.exporter = CsvItemExporter(file)
    #     self.exporter.fields_to_export = [list with Names of fields to export - order is important]
    #     self.exporter.start_exporting()
    #
    # def spider_closed(self, spider):
    #     self.exporter.finish_exporting()
    #     file = self.files.pop(spider)
    #     file.close()
    #
    # def process_item(self, item, spider):
    #     self.exporter.export_item(item)
    #     return item

    # position = scrapy.Field()
    # date = scrapy.Field()
    # open = scrapy.Field()
    # top = scrapy.Field()
    # close = scrapy.Field()
    # lowest = scrapy.Field()
    # volume = scrapy.Field()
    # amount = scrapy.Field()
    # sname = scrapy.Field()
    # def __init__(self):
    #     # 打开文件，指定方式为写，利用第3个参数把csv写数据时产生的空行消除
    #     self.f = open("myproject.csv", "a", newline="")
    #     # 设置文件第一行的字段名，注意要跟spider传过来的字典key名称相同
    #     # self.fieldnames = ["position", "stock_num", "datetime", "opening_price",
    #     # "hightest_prce", "closing_price", "minimum_price",
    #     #                    "trading"]
    #     self.fieldnames = ["position", "sname", "date", "open", "top", "close",
    #                        "lowest", "volume", "amount"]
    #     # 指定文件的写入方式为csv字典写入，参数1为指定具体文件，参数2为指定字段名
    #     self.writer = csv.DictWriter(self.f, fieldnames=self.fieldnames)
    #     # 写入第一行字段名，因为只要写入一次，所以文件放在__init__里面
    #     self.writer.writeheader()
    #
    # def process_item(self, item, spider):
    #     # 写入spider传过来的具体数值
    #     self.writer.writerow(item)
    #     # 写入完返回
    #     return item
    #
    # def close(self, spider):
    #     self.f.close()

    def __init__(self):
        self.client = pymysql.connect(
            host='127.0.0.1',
            port=3306,
            user='root',  # 使用自己的用户名
            passwd='123456',  # 使用自己的密码
            db='stock_info',  # 数据库名
            charset='utf8'
        )
        self.cur = self.client.cursor()

    def process_item(self, item, spider):
        check_sql = "select * from fa_history where date = {} and sname = {}".format(
                    repr(item["date"]), repr(item["sname"])
        )
        count = self.cur.execute(check_sql)
        if not count:
            sql = "insert into fa_history( date, open, top, close ,lowest, volume ,amount, sname) " \
                  "VALUES ({}, {}, {}, {}, {}, {}, {}, {})"
            insert_sql = sql.format(repr(item["date"]), item["open"], item["top"],
                             item["close"], item["lowest"], item["volume"], item["amount"],
                             repr(item["sname"]))
            self.cur.execute(insert_sql)
            self.client.commit()
        else:
            print("*" * 20, "sname, date:{}, {}已经存在".format(item["sname"], item["date"]))
        return item

    def close_spider(self, spider):
        self.client.commit()
        self.cur.close()
        self.client.close()


